import pymysql


class DB:
    def __init__(self, host='localhost', port=3306, db='lzy', user='root', passwd='root', charset='utf8'):
        # 建立连接
        self.conn = pymysql.connect(host=host, port=port, db=db, user=user, passwd=passwd, charset=charset)
        # 当游标建立之时，就自动开始了一个隐形的数据库事务
        self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        # 每执行完一条SQL自动提交
        self.conn.autocommit(True)

        # with语法有用，离开with触发

    def close(self):
        self.conn.commit()
        self.cursor.close()
        self.conn.close()

    # with语法有用，进入with触发
    def __enter__(self):
        return self.cursor

    # with语法有用，离开with触发
    def __exit__(self):
        self.close()

    def insert(self):
        sql = "insert into user values (null, '{}', {})".format('luzhiyuan', 27)
        self.cursor.execute(sql)

    def select(self):
        sql = 'select * from user'
        self.cursor.execute(sql)
        results = self.cursor.fetchall()
        return results


if __name__ == '__main__':
    db = DB()
    # db.insert()
    results = db.select()
    sum_id = 0
    for line in results:
        print('获取结果：', line)
        sum_id = sum_id + line['id']

    print('sum_id=', sum_id)
